package com.huellas.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import com.huellas.bean.DoctorBean;
import com.huellas.bean.UsuarioBean;
import com.huellas.excepcion.DAOExcepcion;
import com.huellas.util.ConexionBD;

public class DoctorDAO extends UsuarioDAO{
	
	//Metodo para insertar doctor
	public void insertarDoctor(DoctorBean doctorBean) throws DAOExcepcion{
		String query = "insert into doctor(CoColegioMedico, usuario_CoUsuario) values (?,?)";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = ConexionBD.obtenerConexion();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setInt(1, doctorBean.getCoColegioMedico());
			preparedStatement.setInt(2, doctorBean.getCoUsuario());
					
			int i = preparedStatement.executeUpdate();
					
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}
			// Obtener el ultimo id
			int id = 0;
			query = "select last_insert_id()";
			preparedStatement = connection.prepareStatement(query);
			resultSet = preparedStatement.executeQuery();
				
			if (resultSet.next()) {
				id = resultSet.getInt(1);
			}
			doctorBean.setCoUsuario(id);
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(resultSet);
			this.cerrarStatement(preparedStatement);
			this.cerrarConexion(connection);
		}
	}
	
	public void modificarDoctor(DoctorBean doctorBean) throws DAOExcepcion{
		String query = "update doctor set CoColegioMedico = ? where usuario_CoUsuario = ?";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		
		try{
			connection = ConexionBD.obtenerConexion();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setInt(1, doctorBean.getCoColegioMedico());
			preparedStatement.setInt(2, doctorBean.getCoUsuario());
			
			int i = preparedStatement.executeUpdate();
			if (i != 1){
				throw new SQLException("No se pudo actualizar");
			}
		}catch(SQLException e){
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		}finally{
			this.cerrarStatement(preparedStatement);
			this.cerrarConexion(connection);
		}
	}
	
	//Metodo para buscar doctor
	public Collection<DoctorBean> buscarDoctor(String NoUsuario, String ApPaterno, String ApMaterno) throws DAOExcepcion{
		String query = "select d.CoColegioMedico, u.NoUsuario, u.ApPaterno, u.ApMaterno, u.Direccion, u.Email, u.Celular, u.NoTipoUsuario from Doctor d "
				       + "join (Usuario u) on (d.usuario_CoUsuario = u.CoUsuario) where u.NoUsuario like ? and u.ApPaterno like ? and u.ApMaterno like ? and u.NoTipoUsuario = 'doctor'";
		Collection<DoctorBean> listaUsuario = new ArrayList<DoctorBean>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
				
		try{
			connection = ConexionBD.obtenerConexion();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, "%" + NoUsuario + "%");
			preparedStatement.setString(2, "%" + ApPaterno + "%");
			preparedStatement.setString(3, "%" + ApMaterno + "%");
			resultSet = preparedStatement.executeQuery();
			
			while(resultSet.next()){
				DoctorBean doctorBean = new DoctorBean();
				doctorBean.setCoColegioMedico(resultSet.getInt("CoColegioMedico"));
				doctorBean.setNoUsuario(resultSet.getString("NoUsuario"));
				doctorBean.setApPaterno(resultSet.getString("ApPaterno"));
				doctorBean.setApMaterno(resultSet.getString("ApMaterno"));
				doctorBean.setDireccion(resultSet.getString("Direccion"));
				doctorBean.setEmail(resultSet.getString("Email"));
				doctorBean.setCelular(resultSet.getInt("Celular"));
				doctorBean.setNoTipoUsuario(resultSet.getString("NoTipoUsuario"));
				listaUsuario.add(doctorBean);
			}
		}catch(SQLException e){
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		}finally{
			this.cerrarResultSet(resultSet);
			this.cerrarStatement(preparedStatement);
			this.cerrarConexion(connection);
		}	
		return listaUsuario;
	}
	
	public DoctorBean obtenerDoctor(int CoUsuario) throws DAOExcepcion{
		DoctorBean doctorBean = new DoctorBean();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		try{
			String query = "select d.CoColegioMedico, u.NoUsuario, u.ApPaterno, u.ApMaterno, u.Direccion, u.Email, u.Celular, u.NoTipoUsuario, from Doctor d "
				            + "join (Usuario u) on (d.usuario_CoUsuario = u.CoUsuario) where d.usuario_CoUsuario = ?";
			connection = ConexionBD.obtenerConexion();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setInt(1, CoUsuario);
			resultSet = preparedStatement.executeQuery();
			
			if (resultSet.next()){
				doctorBean.setCoColegioMedico(resultSet.getInt("CoColegioMedico"));
				doctorBean.setNoUsuario(resultSet.getString("NoUsuario"));
				doctorBean.setApPaterno(resultSet.getString("ApPaterno"));
				doctorBean.setApMaterno(resultSet.getString("ApMaterno"));
				doctorBean.setDireccion(resultSet.getString("Direccion"));
				doctorBean.setEmail(resultSet.getString("Email"));
				doctorBean.setCelular(resultSet.getInt("Celular"));
				doctorBean.setNoTipoUsuario(resultSet.getString("NoTipoUsuario"));	
			}
		}catch(SQLException e){
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		}finally{
			this.cerrarResultSet(resultSet);
			this.cerrarStatement(preparedStatement);
			this.cerrarConexion(connection);
		}
		
		return doctorBean;
	}
	
	public DoctorBean obtenerRegistroDoctor(int CoColegioMedico, String NoUsuario, String ApPaterno, String ApMaterno, String Email, int Celular, String NoTipoUsuario) throws DAOExcepcion{
		DoctorBean doctorBean = new DoctorBean();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		try{
			String query = "select d.CoColegioMedico, u.NoUsuario, u.ApPaterno, u.ApMaterno, u.Direccion, u.Email, u.Celular, u.NoTipoUsuario from Doctor d "
				            + "join (Usuario u) on (d.usuario_CoUsuario = u.CoUsuario) where d.CoColegioMedico = ? and u.NoUsuario like ? and u.ApPaterno like ? "
				            + "and u.ApMaterno like ? and u.Email like ? and u.Celular = ? and u.NoTipoUsuario like ?";
			connection = ConexionBD.obtenerConexion();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setInt(1, CoColegioMedico);
			preparedStatement.setString(2, "%" + NoUsuario + "%");
			preparedStatement.setString(3, "%" + ApPaterno + "%");
			preparedStatement.setString(4, "%" + ApMaterno + "%");
			preparedStatement.setString(5, "%" + Email + "%");
			preparedStatement.setInt(6, Celular);
			preparedStatement.setString(7, "%" + NoTipoUsuario + "%");
			resultSet = preparedStatement.executeQuery();
			
			if (resultSet.next()){
				doctorBean.setCoColegioMedico(resultSet.getInt("CoColegioMedico"));
				doctorBean.setNoUsuario(resultSet.getString("NoUsuario"));
				doctorBean.setApPaterno(resultSet.getString("ApPaterno"));
				doctorBean.setApMaterno(resultSet.getString("ApMaterno"));
				doctorBean.setDireccion(resultSet.getString("Direccion"));
				doctorBean.setEmail(resultSet.getString("Email"));
				doctorBean.setCelular(resultSet.getInt("Celular"));
				doctorBean.setNoTipoUsuario(resultSet.getString("NoTipoUsuario"));	
			}
		}catch(SQLException e){
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		}finally{
			this.cerrarResultSet(resultSet);
			this.cerrarStatement(preparedStatement);
			this.cerrarConexion(connection);
		}
		
		return doctorBean;
	}
	
	//Metodo para obtener el codigo de doctor
	public int obtenerCoDoctor(String NoDoctor) throws DAOExcepcion{
		int coDoctor = 0;
	
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
				
		try{
			String query = "select CoUsuario from Usuario where NoUsuario like ? and NoTipoUsuario like '%doctor%'";
			connection = ConexionBD.obtenerConexion();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, "%" + NoDoctor + "%");
			resultSet = preparedStatement.executeQuery();
					
			if(resultSet.next()){
				coDoctor = resultSet.getInt(1);
			}
		}catch(SQLException e){
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		}finally {
			this.cerrarResultSet(resultSet);
			this.cerrarStatement(preparedStatement);
			this.cerrarConexion(connection);
		}
				
		return coDoctor;
	}

}
